package net.lmc.realtime.traffic.mapper;


import net.lmc.realtime.traffic.bean.SugarIllegalEvents;
import net.lmc.realtime.traffic.bean.SugarTwo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface TrafficMapper {
    @Select("SELECT licensePlate,\n" +
            "       roadId,\n" +
            "       lane,\n" +
            "       CASE WHEN eventType = 'normal' THEN '正常'\n" +
            "               WHEN eventType = 'overspeed' THEN '超速'\n" +
            "               WHEN eventType = 'redlight' THEN '闯红灯'\n" +
            "               WHEN eventType = 'illegal_lane_change' THEN '违法变道'\n" +
            "               ELSE '未知' END AS eventType\n" +
            "FROM traffic_monitoring.dwd_traffic_illegal_events\n" +
            "WHERE toDate(toDateTime(ts / 1000)) = #{date}\n" +
            "ORDER BY ts DESC\n" +
            "LIMIT 10")
    List<SugarIllegalEvents> getIllegalEvents(@Param("date") String date);

    @Select("SELECT CASE WHEN eventType = 'normal' THEN '正常'\n" +
            "            WHEN eventType = 'overspeed' THEN '超速'\n" +
            "            WHEN eventType = 'redlight' THEN '闯红灯'\n" +
            "            WHEN eventType = 'illegal_lane_change' THEN '违法变道'\n" +
            "            ELSE '未知' END AS name,\n" +
            "       sum(count)     AS value\n" +
            "FROM traffic_monitoring.dwd_traffic_illegal_type\n" +
            "WHERE toDate(toDateTime(ts / 1000)) = #{date}\n" +
            "GROUP BY eventType")
    List<SugarTwo> getIllegalType(@Param("date") String date);
}
